﻿/********************************************************************************
* Author:	Christos Polydorou	(xristos.polydoroy@gmail.com)					*
* Author:	Aris Michail		(aris.michail@gmail.com)						*
* Date:		22/9/2011															*
********************************************************************************/

CREATE PROCEDURE [dbo].[userLogin]
	@username nvarchar(50), 
	@password nvarchar(50) 
AS
	DECLARE @id bigint
	DECLARE @enabled int
	DECLARE @loginattempts int
	DECLARE @timelocked datetime
	DECLARE @lastlogin datetime
	DECLARE @userpassword nvarchar(50)
	DECLARE @email nvarchar(50)
	DECLARE @maxloginattempts int
	DECLARE @minstolock int
	DECLARE @usertype nvarchar(3)
	SET @minstolock = 5
	SET @maxloginattempts = 4
	SET @id = -5

	SELECT @id = Users.ID, @userpassword = Users.Password, @loginattempts = Users.LoginAttempts, @usertype = Users.Usertype, @timelocked = Users.TimeLocked, @enabled = IsEnabled, @lastlogin = Users.LastLogin, @email = Users.Email
	FROM Users
	WHERE [Users].Username = @username

	IF @id < 0 
		return -2 
	
	IF @enabled = 0                                                                                         /* user is not enabled */
		return -1
		
	IF @timelocked > DATEADD(MINUTE,-@minstolock,GETDATE())													/* user is not disabled, the password is correct */
		RETURN -3
		
	IF @userpassword != @password																			/* wrong password */
		BEGIN
			IF @loginattempts < @maxloginattempts															/* do not lock user if attempts are less than 5 */                                 
				BEGIN
					SET @loginattempts = @loginattempts + 1
					UPDATE Users
					SET users.LoginAttempts = @loginattempts
					WHERE Users.ID = @id
					
					SET @loginattempts = @loginattempts - 1													/* adjust the @loginattempts value for the following if statement */                                 
				END
				
			IF @loginattempts = @maxloginattempts                                                                                    
				BEGIN
					SET @loginattempts = @loginattempts + 1
						UPDATE Users																		/* lock user at the next attempt */
						SET users.TimeLocked = CURRENT_TIMESTAMP, users.LoginAttempts = @loginattempts
						WHERE Users.ID = @id
						
						SET @loginattempts = @loginattempts - 1												/* adjust the @loginattempts value for the following if statement */
				END
			IF @loginattempts > @maxloginattempts
				BEGIN
					UPDATE Users																			/* lock user at the next attempt */
					SET users.LoginAttempts = 1
					WHERE Users.ID = @id
				END
			RETURN -2
		END
		
	UPDATE Users                                                                                        /* user is enabled, not locked and the password is correct */
	SET users.LastLogin = CURRENT_TIMESTAMP, users.LoginAttempts = 0
	WHERE Users.ID = @id
	SET NOCOUNT OFF
	RETURN @id
	GO 